package cn.doitedu.dwetl

import org.apache.spark.sql.SparkSession
import cn.doitedu.dwetl.utils.RrbitmapOrAggregationFunction
import cn.doitedu.dwetl.utils.RrUtils._
/**
 * @author 涛哥
 * @nick_name "deep as the sea"
 * @contact qq:657270652 wx:doit_edu
 * @site www.doitedu.cn
 * @date 2021-01-17
 * @desc 在bitmap聚合基础之上，计算多维cube最终结果
 *      目标表：
-- 流量基础指标，多维分析报表
create table dws.tfc_overview_bitmapcube(
  province           string,
  city               string,
  region             string,
  hour_range         int   ,
  device_type        string,
  os_name            string,
  start_page         string,
  end_page           string,
  isnew              string,
  pv_cnt             bigint,
  uv_cnt             bigint,
  session_cnt        bigint,
  time_long          bigint,
  return_user_cnt    bigint,
  jumpout_cnt        bigint,
  jumpout_user_cnt   bigint
)
PARTITIONED BY (dt string)
STORED AS PARQUET
;
 *
 *
 */
object TrafficBasicMetricCubeCalc {

  def main(args: Array[String]): Unit = {
    val spark = SparkSession.builder()
      .appName("多维分析计算（利用bitmap做逐层聚合）")
      .enableHiveSupport()
      //.master("local[*]")
      .getOrCreate()
    import org.apache.spark.sql.functions._
    import spark.implicits._


    spark.udf.register("rr_or",RrbitmapOrAggregationFunction)
    spark.udf.register("rr_card",getCard _)
    spark.sql(
      s"""
        |
        |INSERT INTO TABLE dws.tfc_overview_bitmapcube PARTITION(dt='${args(0)}')
        |SELECT
        |  province     ,
        |  city         ,
        |  region       ,
        |  hour_range   ,
        |  device_type  ,
        |  os_name      ,
        |  start_page   ,
        |  end_page     ,
        |  isnew        ,
        |  sum(pv_cnt) as pv_cnt,
        |  rr_card(rr_or(uv_bitmap)) as uv_cnt,
        |  sum(session_cnt) as session_cnt,
        |  sum(time_long) as time_long,
        |  rr_card(rr_or(return_user_bitmap)) as return_user_cnt,
        |  sum(jumpout_cnt) as jumpout_cnt,
        |  rr_card(rr_or(jumpout_user_bitmap)) as jumpout_user_cnt
        |
        |FROM dws.tfc_overview_bitmap
        |GROUP BY
        |  province     ,
        |  city         ,
        |  region       ,
        |  hour_range   ,
        |  device_type  ,
        |  os_name      ,
        |  start_page   ,
        |  end_page     ,
        |  isnew
        |WITH CUBE
        |
        |
        |""".stripMargin)

    /**
     * 维度报表结果查询示例，比如，查询按手机型号统计的各指标
     *
     *
    SELECT  *  FROM DWS.tfc_overview_bitmapcube  t
        where coalesce(
          province     ,
          city         ,
          region       ,
          hour_range   ,
          os_name      ,
          start_page   ,
          end_page     ,
          isnew
        ) is null and device_type is not null
     *
     *
     */


    spark.close()

  }
}
